<?php

namespace app\controller;

use think\db\exception\DbException;
use think\facade\Db;
use think\response\Json;

class Intricatetest
{
    /**
     * @return Json
     * @throws DbException
     */
    public function test1(): Json
    {
        $query = [];
        $query[] = ['age', '>', 18];
        $query[] = ['age', '<', 30];
        // $query[] = ['age', 'between', [18, 30]]; // 效果一样，但是我觉得 between 更好
        $res = Db::name('test')->where($query)->select();
        $sql = Db::getLastSql();
        return json(['res' => $res, 'sql' => $sql]);
    }

    /**
     * @return Json
     * @throws DbException
     */
    public function test2(): Json
    {
        $query = [];
        $query[] = ['name', 'like', '\btest\b'];
        // $query[] = ['name', 'regex', '\bgdp\b']; // like 与 regex 是一样的
        $res = Db::name('test')->where($query)
            // ->order('age', 'desc')
            ->order(['age' => 1]) // 这里直接使用 1 或者 -1；框架没有把 asc 和 desc 转换成 1 和 -1
            ->select();
        $sql = Db::getLastSql();
        return json(['res' => $res, 'sql' => $sql]);
    }

    /**
     * @return Json
     */
    public function test3(): Json
    {
        $query = [];
        $query[] = ['age', 'between', [18, 30]]; // 筛选条件
        $aggregate = 'sum'; // 聚合操作，这里是求和
        $field = 'age'; // 要聚合的字段
        $res = Db::name('test')->where($query)->aggregate($aggregate, $field);
        $sql = Db::getLastSql();
        return json(['res' => $res, 'sql' => $sql]);
    }

    /**
     * @return Json
     */
    public function test4(): Json
    {
        $query = [];
        $query[] = ['age', 'between', [18, 30]]; // 筛选条件
        $aggregate = ['sum' => 'age']; // 聚合操作，这里是求和
        $groupBy = ['age']; // 字段值相同的数据，进行分组
        // vendor/topthink/think-orm/src/db/Mongo.php::multiAggregate
        $res = Db::name('test')->where($query)->multiAggregate($aggregate, $groupBy);
        $sql = Db::getLastSql();
        return json(['res' => $res, 'sql' => $sql]);
    }

    /**
     * @return Json
     */
    public function test5(): Json
    {
        $command = [
            'aggregate' => 'test',
            'pipeline' => [
                ['$match' => ['age' => ['$gte' => 18, '$lte' => 28],],],
                ['$project' => ['age' => 1, 'name' => 1,]],
                ['$group' => ['_id' => '$age', 'name' => ['$first' => '$name'], 'sum' => ['$sum' => '$age'],],],
            ],
            'cursor' => new \stdClass,
        ];
        // vendor/topthink/think-orm/src/db/Mongo.php::cmd
        $res = Db::name('test')->cmd($command);
        $sql = Db::getLastSql();
        return json(['res' => $res, 'sql' => $sql]);
    }
}